package dao.mysql;

import entity.Favorite;
import entity.PlayCount;
import entity.PlayHistory;
import entity.Video;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

public class VideoMySQLDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int add(Video v) throws Exception {
        v.setD_time(new Date().getTime());
        KeyHolder keyHolder = new GeneratedKeyHolder();
        String sql = "INSERT INTO video (d_title, d_time, d_description, d_username, d_video, d_cover) VALUE (?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, v.getD_title());
                ps.setLong(2, v.getD_time());
                ps.setString(3, v.getD_description());
                ps.setString(4, v.getD_username());
                ps.setString(5, v.getD_video());
                ps.setString(6, v.getD_cover());
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public Video query(int id) throws Exception {
        String sql = "SELECT * FROM video WHERE d_id = ?";
        BeanPropertyRowMapper<Video> rowMapper = new BeanPropertyRowMapper<>(Video.class);
        Object[] params = new Object[]{id};
        List<Video> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list.size() == 1 ? list.get(0) : null;
    }

    private static long MIN_SPACE_TIME = 180 * 1000;

    public void videoPlay(String username, int id) throws Exception {
        String sql = "SELECT * FROM playHistory WHERE d_username = ? AND d_time > ?";
        BeanPropertyRowMapper<PlayHistory> rowMapper = new BeanPropertyRowMapper<>(PlayHistory.class);
        Object[] params = new Object[]{username, new Date().getTime() - MIN_SPACE_TIME};
        if (this.jdbcTemplate.query(sql, params, rowMapper).size() > 0) return;

        sql = "INSERT INTO playHistory (d_username, d_time, d_id) VALUE (?, ?, ?)";
        params = new Object[]{username, new Date().getTime(), id};
        jdbcTemplate.update(sql, params);
    }

    public int playCount(int id) throws Exception {
        String sql = "SELECT * FROM playHistory WHERE d_id = ?";
        BeanPropertyRowMapper<PlayHistory> rowMapper = new BeanPropertyRowMapper<>(PlayHistory.class);
        Object[] params = new Object[]{id};
        return this.jdbcTemplate.query(sql, params, rowMapper).size();
    }

    public int favoriteCount(int id) throws Exception {
        String sql = "SELECT * FROM favorite WHERE d_id = ? AND d_favorite = ?";
        BeanPropertyRowMapper<PlayHistory> rowMapper = new BeanPropertyRowMapper<>(PlayHistory.class);
        Object[] params = new Object[]{id, 1};
        return this.jdbcTemplate.query(sql, params, rowMapper).size();
    }

    public void setFavorite(String username, int id, boolean favorite) {
        String sql = "UPDATE favorite SET d_favorite = ? WHERE d_username = ? AND d_id = ?";
        Object[] params = new Object[]{favorite, username, id};
        if(this.jdbcTemplate.update(sql, params) == 0) {
            sql = "INSERT INTO favorite (d_username, d_id, d_favorite) VALUE (?, ?, ?)";
            params = new Object[]{username, id, favorite};
            this.jdbcTemplate.update(sql, params);
        }
    }

    public boolean isFavorite(String username, int id) {
        String sql = "SELECT * FROM favorite WHERE d_username = ? AND d_id = ?";
        Object[] params = new Object[]{username, id};
        BeanPropertyRowMapper<Favorite> rowMapper = new BeanPropertyRowMapper<>(Favorite.class);
        List<Favorite> list = this.jdbcTemplate.query(sql, params, rowMapper);
        if(list.size() == 0) return false;
        return list.get(0).getD_favorite() == 1;
    }

    public List<Video> latest(int count) {
        String sql = "SELECT * FROM video ORDER BY d_time DESC LIMIT ?";
        BeanPropertyRowMapper<Video> rowMapper = new BeanPropertyRowMapper<>(Video.class);
        Object[] params = new Object[]{count};
        return this.jdbcTemplate.query(sql, params, rowMapper);
    }

    private static int interval = 1000 * 60 * 60 * 72;

    public List<PlayCount> hot(int count) {
        String sql = "SELECT * FROM playHistory WHERE d_time > ? ORDER BY d_id DESC";
        BeanPropertyRowMapper<PlayHistory> rowMapper = new BeanPropertyRowMapper<>(PlayHistory.class);
        Object[] params = new Object[] { new Date().getTime() - interval };
        List<PlayHistory> historyList = this.jdbcTemplate.query(sql, rowMapper, params);
        List<PlayCount> countList = new ArrayList<PlayCount>();
        boolean has;
        for(PlayHistory i : historyList) {
            has = false;
            for(PlayCount j : countList) {
                if(j.getD_id() == i.getD_id()) {
                    j.setD_count(j.getD_count() + 1);
                    has = true;
                    break;
                }
            }
            if(!has) {
                countList.add(new PlayCount(i.getD_id(), 1));
            }
        }
        countList.sort(Comparator.comparing(PlayCount::getD_count).reversed());
        if(countList.size() <= count) return countList.subList(0, countList.size());
        return countList.subList(0, count);
    }

    public List<Video> search(String[] key) {
        List<Video> list = new ArrayList<>();
        for(String s : key) {
            String sql = "SELECT * FROM video WHERE d_title LIKE ?";
            BeanPropertyRowMapper<Video> rowMapper = new BeanPropertyRowMapper<>(Video.class);
            Object[] params = new Object[] { "%" + s + "%" };
            List<Video> result = this.jdbcTemplate.query(sql, params, rowMapper);
            for(Video v : result) {
                boolean has = false;
                for(Video w : list) {
                    if(v.getD_id() == w.getD_id()) {
                        has = true;
                        break;
                    }
                }
                if(!has) list.add(v);
            }
        }
        return list;
    }
}
